using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using Microsoft.ApplicationBlocks.Data;
using System.Data;
using Microsoft.AceEngineering.SPM.SPMCustomDataEntity;
using Microsoft.AceEngineering.SPM.SPMDataLayer;

namespace Microsoft.AceEngineering.SPM.SPMDataLayer
{
    public class OrganizationDL
    {
        public static List<OrganizationCDE> GetChildOrgs(int parentID)
        {
            SqlDataReader sqlreader = null;
            List<OrganizationCDE> orgCollection = null;
            OrganizationCDE orgInstance = null;

            try
            {
                string conString = ConfigurationManager.ConnectionStrings["SPMSQL"].ConnectionString;

                SqlParameter[] myParams = new SqlParameter[1];
                myParams[0] = new SqlParameter("@parentID", SqlDbType.Int);
                myParams[0].Value = parentID;

                sqlreader = SqlHelper.ExecuteReader(conString, CommandType.StoredProcedure, "APM_spSelectChildOrganizations", myParams);

                orgCollection = new List<OrganizationCDE>();
                while (sqlreader.Read())
                {
                    orgInstance = new OrganizationCDE();
                    orgInstance.OrgID = (int)sqlreader.GetValue(0);
                    orgInstance.OrgName = sqlreader.GetString(1);
                    orgInstance.ChildCount = (int)sqlreader.GetValue(2);
                    orgCollection.Add(orgInstance);
                    orgInstance = null;
                }
            }
            finally
            {
                if (sqlreader != null)
                {
                    if (!sqlreader.IsClosed)
                        sqlreader.Close();
                }
            }
            return orgCollection;
        }

        public static OrganizationCDE GetOrgByOrgID(int orgID)
        {
            SqlDataReader sqlreader = null;
            OrganizationCDE org = new OrganizationCDE();

            try
            {
                string conString = ConfigurationManager.ConnectionStrings["SPMSQL"].ConnectionString;

                SqlParameter[] myParams = new SqlParameter[1];
                myParams[0] = new SqlParameter("@OrgID", SqlDbType.Int);
                myParams[0].Value = orgID;

                sqlreader = SqlHelper.ExecuteReader(conString, CommandType.StoredProcedure, "SPM_spGetOrgByOrgID", myParams);

                sqlreader.Read();
                org.OrgID = Int32.Parse(sqlreader["OrgID"].ToString());
                org.OrgName = sqlreader["OrgName"].ToString();
                org.ChildCount = Int32.Parse(sqlreader["OrgUseage"].ToString());
            }
            finally
            {
                if (sqlreader != null)
                {
                    if (!sqlreader.IsClosed)
                        sqlreader.Close();
                }
            }
            return org;
        }

        public static void UpdateOrgByOrgID(int orgID, string orgName, int parentOrgID, string modifiedBy)
        {
            string conString = ConfigurationManager.ConnectionStrings["SPMSQL"].ConnectionString;

            SqlParameter[] myParams = new SqlParameter[4];
            myParams[0] = new SqlParameter("@OrgID", SqlDbType.Int);
            myParams[0].Value = orgID;

            myParams[1] = new SqlParameter("@OrgName", SqlDbType.VarChar, 50);
            myParams[1].Value = orgName;

            myParams[2] = new SqlParameter("@ParentOrgID", SqlDbType.Int);
            myParams[2].Value = parentOrgID;

            myParams[3] = new SqlParameter("@ModifiedBy", SqlDbType.VarChar, 50);
            myParams[3].Value = modifiedBy;

            SqlHelper.ExecuteNonQuery(conString, CommandType.StoredProcedure, "SPM_spUpdateOrgByOrgID", myParams);
        }

        public static void DeleteOrgByOrgID(int orgID)
        {
            string conString = ConfigurationManager.ConnectionStrings["SPMSQL"].ConnectionString;

            SqlParameter[] myParams = new SqlParameter[1];
            myParams[0] = new SqlParameter("@OrgID", SqlDbType.Int);
            myParams[0].Value = orgID;

            SqlHelper.ExecuteNonQuery(conString, CommandType.StoredProcedure, "SPM_spDeleteOrgByOrgID", myParams);
        }
    }
}
